// <copyright file="Database.cs" company="Sinclair Community College">
// Copyright (c) Sinclair Community College. All rights reserved.
// </copyright>

namespace SinclairCC.AppVSuite.DuplicateRootDetector
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlCompactDatabase;
    using System.Data.SqlServerCe;
    using System.IO;
    using System.Linq;
    using System.Xml;

    /// <summary>
    /// Defines a database that stores information about App-V .SFT files.
    /// </summary>
    public class Database : System.Data.SqlCompactDatabase.SqlCeDatabase
    {
        /// <summary>
        /// Initializes a new instance of the Database class.
        /// </summary>
        public Database()
#if DEBUG
            : base(System.IO.Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.DesktopDirectory), "Duplicate Root Folder Finder.sdf"))
#endif
        {
#if DEBUG
            this.DeleteFileOnDisposition = false;
#else
            this.DeleteFileOnDisposition = true;
#endif

            this.InitializeDataTables();
        }

        /// <summary>
        /// A delegate type for hooking up status change notifications. 
        /// </summary>
        /// <param name="sender">
        /// The source of the event.
        /// </param>
        /// <param name="newStatus">
        /// The status of the search operation.
        /// </param>
        public delegate void StatusChangedEventHandler(object sender, SearchStatus newStatus);

        /// <summary>
        /// Notifies consumers that the status of the search operation has changed.
        /// </summary>
        public event StatusChangedEventHandler StatusChanged;

        /// <summary>
        /// Gets a DataTable containing the errors that were encountered during the folder examination process.
        /// </summary>
        public DataTable ErrorTable
        {
            get
            {
                DataTable errorTable = new DataTable();

                System.Data.SqlServerCe.SqlCeDataAdapter adapter = new SqlCeDataAdapter("SELECT * FROM Errors", this.Connection);
                adapter.Fill(errorTable);
                adapter.Dispose();

                return errorTable;
            }
        }

        /// <summary>
        /// Gets a value indicating whether errors were encountered during the folder examination process.
        /// </summary>
        public bool HasErrors
        {
            get
            {
                System.Data.SqlServerCe.SqlCeCommand countCommand = this.Connection.CreateCommand();
                countCommand.CommandText = "SELECT COUNT(filePath) AS FileCount FROM Errors";
                int count = (int)countCommand.ExecuteScalar();
                countCommand.Dispose();
                return count > 0;
            }
        }

        /// <summary>
        /// Gets the number of SFT files about which the database has information.
        /// </summary>
        public int SftFileCount
        {
            get
            {
                System.Data.SqlServerCe.SqlCeCommand countCommand = this.Connection.CreateCommand();
                countCommand.CommandText = "SELECT COUNT(SftPath) FROM Files";
                int count = (int)countCommand.ExecuteScalar();
                countCommand.Dispose();
                return count;
            }
        }

        /// <summary>
        /// Gets the total size of the all of the SFT files about which the database has information.
        /// </summary>
        public long TotalSftFileSize
        {
            get
            {
                System.Data.SqlServerCe.SqlCeCommand sumCommand = this.Connection.CreateCommand();
                sumCommand.CommandText = "SELECT SUM(Size) FROM Files";
                long sum = 0;
                object returnValue = sumCommand.ExecuteScalar();
                if (returnValue != DBNull.Value)
                {
                    sum = (long)returnValue;
                }
                sumCommand.Dispose();
                return sum;
            }
        }

        /// <summary>
        /// Gets a list of the root folder names that have been duplicated.
        /// </summary>
        public string[] DuplicatedRootFolderNames
        {
            get
            {
                SqlCeCommand selectCommand = this.CreateCommand();

                selectCommand.CommandText = "SELECT DISTINCT RootFolder FROM Files WHERE (RootFolder IN (SELECT RootFolder FROM (SELECT RootFolder, SftPath FROM Files AS Files_2 GROUP BY RootFolder, SftPath HAVING (RootFolder IN (SELECT RootFolder FROM Files AS Files_1 GROUP BY RootFolder HAVING (COUNT(RootFolder) > 1)))) AS derivedtbl_1 GROUP BY RootFolder HAVING (COUNT(RootFolder) <> 1))) ORDER BY RootFolder";
                SqlCeDataAdapter adapter = new SqlCeDataAdapter(selectCommand);
                DataTable rootFolderTable = new DataTable("RootFolderPaths");
                adapter.Fill(rootFolderTable);
                adapter.Dispose();

                List<string> rootPaths = new List<string>();
                foreach (DataRow rootFolderRow in rootFolderTable.Rows)
                {
                    string rootFolderPath = (string)rootFolderRow["RootFolder"];

                    selectCommand.CommandText = "SELECT DISTINCT SftPath FROM Files WHERE (RootFolder = '" + rootFolderPath + "')";
                    adapter = new SqlCeDataAdapter(selectCommand);
                    DataTable sftTable = new DataTable("SFT");
                    adapter.Fill(sftTable);
                    adapter.Dispose();

                    List<string> parentFolderPaths = new List<string>();
                    foreach (DataRow sftRow in sftTable.Rows)
                    {
                        string sftPath = (string)sftRow["SftPath"];
                        string parentPath = System.IO.Path.GetDirectoryName(sftPath);
                        if (!parentFolderPaths.Exists(new CaseInsensitiveFind(parentPath).Match))
                        {
                            parentFolderPaths.Add(parentPath);
                        }
                    }

                    if (parentFolderPaths.Count > 1)
                    {
                        rootPaths.Add(rootFolderPath);
                    }

                    sftTable.Dispose();
                }

                rootFolderTable.Dispose();
                selectCommand.Dispose();

                string[] returnArray = new string[rootPaths.Count];
                rootPaths.CopyTo(returnArray);
                return returnArray;
            }
        }

        /// <summary>
        /// Gets a list of the package IDs that have been duplicated.
        /// </summary>
        public Guid[] DuplicatedPackageIds
        {
            get
            {
                SqlCeCommand selectCommand = this.CreateCommand();

                selectCommand.CommandText = "SELECT DISTINCT PackageId FROM Files WHERE (PackageId IN (SELECT PackageId FROM (SELECT PackageId, SftPath FROM Files AS Files_2 GROUP BY PackageId, SftPath HAVING (PackageId IN (SELECT PackageId FROM Files AS Files_1 GROUP BY PackageId HAVING (COUNT(PackageId) > 1)))) AS derivedtbl_1 GROUP BY PackageId HAVING (COUNT(PackageId) <> 1))) ORDER BY PackageId";
                SqlCeDataAdapter adapter = new SqlCeDataAdapter(selectCommand);
                DataTable packageIdTable = new DataTable("PackageIDs");
                adapter.Fill(packageIdTable);
                adapter.Dispose();

                List<Guid> packageIDs = new List<Guid>();
                foreach (DataRow packageIdRow in packageIdTable.Rows)
                {
                    Guid packageId = (Guid)packageIdRow["PackageId"];

                    selectCommand.CommandText = "SELECT DISTINCT SftPath FROM Files WHERE (PackageId = '" + packageId + "')";
                    adapter = new SqlCeDataAdapter(selectCommand);
                    DataTable sftTable = new DataTable("SFT");
                    adapter.Fill(sftTable);
                    adapter.Dispose();

                    List<string> parentFolderPaths = new List<string>();
                    foreach (DataRow sftRow in sftTable.Rows)
                    {
                        string sftPath = (string)sftRow["SftPath"];
                        string parentPath = System.IO.Path.GetDirectoryName(sftPath);
                        if (!parentFolderPaths.Exists(new CaseInsensitiveFind(parentPath).Match))
                        {
                            parentFolderPaths.Add(parentPath);
                        }
                    }

                    if (parentFolderPaths.Count > 1)
                    {
                        packageIDs.Add(packageId);
                    }

                    sftTable.Dispose();
                }

                packageIdTable.Dispose();
                selectCommand.Dispose();

                Guid[] returnArray = new Guid[packageIDs.Count];
                packageIDs.CopyTo(returnArray);
                return returnArray;
            }
        }

        /// <summary>
        /// Examines the contents of the specified folder.
        /// </summary>
        /// <param name="folderPath">
        /// The path of the folder to be examined.
        /// </param>
        public void SurveyDirectory(string folderPath)
        {
            // If the folder does not exist, abort this function.
            if (!System.IO.Directory.Exists(folderPath))
            {
                return;
            }

            this.OnStatusChanged(new SearchStatus("Examining " + folderPath + "."));

            System.IO.DirectoryInfo dirInfo = new System.IO.DirectoryInfo(folderPath);

            // This will store the path of the file being examined.
            string filePath;

            // Attempt to loop through the contents of the folder, getting information
            // about the files contained therein.
            try
            {
                using (SqlCeTransaction mytransaction = this.BeginTransaction())
                {
                    using (SqlCeCommand insertFileCommand = this.CreateCommand())
                    {
                        insertFileCommand.CommandText = "INSERT INTO Files (SftPath, RootFolder, RootFolder8dot3, PackageId, Created, LastModified, Size) VALUES (@path, @root, @root8dot3, @packageId, @created, @modified, @size);";
                        insertFileCommand.Parameters.Add("@path", SqlDbType.NVarChar, 255);
                        insertFileCommand.Parameters.Add("@root", SqlDbType.NVarChar, 255);
                        insertFileCommand.Parameters.Add("@root8dot3", SqlDbType.NVarChar, 12);
                        insertFileCommand.Parameters.Add("@packageId", SqlDbType.UniqueIdentifier);
                        insertFileCommand.Parameters.Add("@created", SqlDbType.DateTime);
                        insertFileCommand.Parameters.Add("@modified", SqlDbType.DateTime);
                        insertFileCommand.Parameters.Add("@size", SqlDbType.BigInt);

                        System.IO.FileInfo[] fileInfos = dirInfo.GetFiles("*.sft");

                        // Loop through the file information objects, recording the information in the DataTables.
                        foreach (System.IO.FileInfo fi in fileInfos)
                        {
                            // Determine the full path of the file. This is done this way rather than
                            // through the FileInfo object's FullName property to avoid PathTooLongExceptions.
                            filePath = System.IO.Path.Combine(dirInfo.FullName, fi.Name);
                            try
                            {
                                SinclairCC.AppV.SftFile sft = new SinclairCC.AppV.SftFile(filePath);
                                insertFileCommand.Parameters["path"].Value = filePath;

                                SinclairCC.AppV.DirectoryMapEntry rootEntry = sft.CompressedIndex.DirectoryEntries.FindRootEntry();
                                insertFileCommand.Parameters["root"].Value = rootEntry.LongFileName;
                                insertFileCommand.Parameters["root8dot3"].Value = rootEntry.ShortFileName;
                                rootEntry = null;

                                insertFileCommand.Parameters["packageId"].Value = sft.Index.PackageId;
                                insertFileCommand.Parameters["created"].Value = sft.Smff.CreationTimeStamp.ToLocalTime();
                                insertFileCommand.Parameters["modified"].Value = sft.Smff.ModificationTimeStamp.ToLocalTime();
                                insertFileCommand.Parameters["size"].Value = new System.IO.FileInfo(filePath).Length;
                                insertFileCommand.ExecuteNonQuery();
                                sft.Dispose();
                            }
                            catch (InvalidOperationException invalidOpError)
                            {
                                this.LogError(filePath, invalidOpError.Message);
                            }
                            catch (System.IO.PathTooLongException pathError)
                            {
                                this.LogError(filePath, pathError.Message);
                            }
                            catch (System.IO.FileNotFoundException fnfError)
                            {
                                System.Text.StringBuilder message = new System.Text.StringBuilder(fnfError.Message);

                                // Add the length of the file path to the error message. This will help diagnose problems
                                // with files whose path is too long for Windows to handle.
                                message.Append(" The length of the path is ");
                                message.Append(filePath.Length);
                                message.Append(" characters.");

                                this.LogError(filePath, message.ToString());
                            }
                        }
                    }

                    mytransaction.Commit();
                }

                // Loop through subdirectories of the current directory, avoiding reparse points (junctions).
                foreach (var subDirInfo in from d in dirInfo.GetDirectories() where (d.Attributes & FileAttributes.ReparsePoint) != FileAttributes.ReparsePoint select d)
                {
                    this.SurveyDirectory(subDirInfo.FullName);
                }
            }
            catch (UnauthorizedAccessException uae)
            {
                this.LogError(folderPath, uae.Message);
            }
        }

        /// <summary>
        /// Gets information about the SFT files that use the specified root folder path.
        /// </summary>
        /// <param name="rootFolderName">
        /// The target root folder path for which corresponding SFT file information is to be retrieved.
        /// </param>
        /// <returns>
        /// Returns a DataTable containing data about the SFT files.
        /// </returns>
        public DataTable GetSftInfoForRootFolder(string rootFolderName)
        {
            DataTable fileTable = new DataTable();

            System.Data.SqlServerCe.SqlCeDataAdapter adapter = new SqlCeDataAdapter("SELECT * FROM Files WHERE (RootFolder = '" + rootFolderName + "')", this.Connection);
            adapter.Fill(fileTable);
            adapter.Dispose();
            
            return fileTable;
        }

        public DataTable GetSftInfoForPackageId(Guid packageId)
        {
            DataTable fileTable = new DataTable();

            System.Data.SqlServerCe.SqlCeDataAdapter adapter = new SqlCeDataAdapter("SELECT * FROM Files WHERE (PackageId = '" + packageId.ToString() + "')", this.Connection);
            adapter.Fill(fileTable);
            adapter.Dispose();

            return fileTable;
        }
        
        /// <summary>
        /// Writes the SFT file information contained in the database to an XML file.
        /// </summary>
        /// <param name="filePath">
        /// The path of the file to which the data should be written.
        /// </param>
        public void WriteXmlFile(string filePath)
        {
            System.Xml.XmlDocument doc = new System.Xml.XmlDocument();

            doc.AppendChild(doc.CreateXmlDeclaration("1.0", null, null));
            doc.AppendChild(doc.CreateComment(string.Format("This file generated {0:F}.", DateTime.Now)));
            System.Xml.XmlElement rootElement = doc.CreateElement("sftFiles");
            doc.AppendChild(rootElement);

            SqlCeCommand selectCommand = this.CreateCommand();
            selectCommand.CommandText = "SELECT * FROM Files";
            DataTable rootFolderTable = new DataTable();
            SqlCeDataAdapter adapter = new SqlCeDataAdapter(selectCommand);
            adapter.Fill(rootFolderTable);
            adapter.Dispose();
            selectCommand.Dispose();

            for (int i = 0; i < rootFolderTable.Rows.Count; i++)
            {
                string sftPath = (string)rootFolderTable.Rows[i]["SftPath"];

                string rootFolderName = (string)rootFolderTable.Rows[i]["RootFolder"];
                string rootFolder8dot3Name = (string)rootFolderTable.Rows[i]["RootFolder8dot3"];

                DateTime created = (DateTime)rootFolderTable.Rows[i]["Created"];
                DateTime lastModified = (DateTime)rootFolderTable.Rows[i]["LastModified"];
                long size = (long)rootFolderTable.Rows[i]["Size"];

                XmlElement folderElement = doc.CreateElement("file");

                XmlAttribute pathAttribute = doc.CreateAttribute("path");
                pathAttribute.Value = sftPath;
                folderElement.Attributes.Append(pathAttribute);

                XmlAttribute rootFolderAttribute = doc.CreateAttribute("rootFolder");
                rootFolderAttribute.Value = rootFolderName;
                folderElement.Attributes.Append(rootFolderAttribute);

                XmlAttribute rootFolder8dot3Attribute = doc.CreateAttribute("rootFolder8dot3");
                rootFolder8dot3Attribute.Value = rootFolder8dot3Name;
                folderElement.Attributes.Append(rootFolder8dot3Attribute);

                XmlAttribute createdAttribute = doc.CreateAttribute("created");
                createdAttribute.Value = System.Xml.XmlConvert.ToString(created, XmlDateTimeSerializationMode.Local);
                folderElement.Attributes.Append(createdAttribute);

                XmlAttribute modifiedAttribute = doc.CreateAttribute("modified");
                modifiedAttribute.Value = System.Xml.XmlConvert.ToString(lastModified, XmlDateTimeSerializationMode.Local);
                folderElement.Attributes.Append(modifiedAttribute);

                XmlAttribute sizeAttribute = doc.CreateAttribute("size");
                sizeAttribute.Value = System.Xml.XmlConvert.ToString(size);
                folderElement.Attributes.Append(sizeAttribute);

                rootElement.AppendChild(folderElement);
            }

            rootFolderTable.Dispose();

            doc.Save(filePath);
        }

        /// <summary>
        /// Triggers the change in status event, if there are any consumers.
        /// </summary>
        /// <param name="newStatus">
        /// The status of the seatch operation.
        /// </param>
        protected virtual void OnStatusChanged(SearchStatus newStatus)
        {
            if (this.StatusChanged != null)
            {
                this.StatusChanged(this, newStatus);
            }
        }

        /// <summary>
        /// Logs an error to the errors table.
        /// </summary>
        /// <param name="filePath">
        /// The path of the file for which the error occurred.
        /// </param>
        /// <param name="message">
        /// Additional information about the error.
        /// </param>
        /// <remarks>
        /// The Message parameter will typically be from a System.Exception or descendant object.
        /// </remarks>
        private void LogError(string filePath, string message)
        {
            SqlCeCommand insertFileCommand = this.CreateCommand();

            insertFileCommand.CommandText = "INSERT INTO Errors (filePath, message) VALUES (@path, @message)";

            SqlCeParameter pathParam = insertFileCommand.Parameters.Add("@path", SqlDbType.NVarChar, 255);
            pathParam.Value = (filePath.Length > pathParam.Size) ? this.ConvertToEllipticalPath(filePath) : filePath;

            SqlCeParameter messageParam = insertFileCommand.Parameters.Add("@message", SqlDbType.NVarChar, 400);
            messageParam.Value = (message.Length > messageParam.Size) ? message.Substring(0, messageParam.Size) : message;

            insertFileCommand.ExecuteScalar();

            insertFileCommand.Dispose();
        }

        /// <summary>
        /// Converts a file system path to an elliptical one by repeatedly replacing the
        /// longest-named part of the path with an ellipsis, until the path's length is 255 or less.
        /// </summary>
        /// <param name="path">
        /// The path to be converted an elliptical one.
        /// </param>
        /// <returns>
        /// Returns the elliptical version of the given path.
        /// </returns>
        private string ConvertToEllipticalPath(string path)
        {
            string[] pathParts = path.Split(new char[] { System.IO.Path.DirectorySeparatorChar, System.IO.Path.AltDirectorySeparatorChar });

            while (path.Length > 255)
            {
                int longestPartIndex = 1;
                for (int i = 2; i < (pathParts.Length - 1); i++)
                {
                    if (pathParts[i].Length > pathParts[longestPartIndex].Length)
                    {
                        longestPartIndex = i;
                    }
                }

                pathParts[longestPartIndex] = "...";
                System.Text.StringBuilder pathBuilder = new System.Text.StringBuilder();
                for (int i = 0; i < pathParts.Length; i++)
                {
                    pathBuilder.Append(pathParts[i]);
                    if (i < (pathParts.Length - 1))
                    {
                        pathBuilder.Append(System.IO.Path.DirectorySeparatorChar);
                    }
                }

                path = pathBuilder.ToString();
            }

            return path;
        }

        /// <summary>
        /// Configures the tables in the database.
        /// </summary>
        private void InitializeDataTables()
        {
            this.OnStatusChanged(new SearchStatus("Initializing database tables."));

            List<System.Data.SqlCompactDatabase.SqlCeColumn> columns = new List<System.Data.SqlCompactDatabase.SqlCeColumn>();
            columns.Add(new System.Data.SqlCompactDatabase.SqlCeColumn("SftPath", SqlDbType.NVarChar, 255));
            columns.Add(new System.Data.SqlCompactDatabase.SqlCeColumn("RootFolder", SqlDbType.NVarChar, 255));
            columns.Add(new System.Data.SqlCompactDatabase.SqlCeColumn("RootFolder8dot3", SqlDbType.NVarChar, 12));
            columns.Add(new System.Data.SqlCompactDatabase.SqlCeColumn("PackageId", SqlDbType.UniqueIdentifier));
            columns.Add(new System.Data.SqlCompactDatabase.SqlCeColumn("Created", SqlDbType.DateTime));
            columns.Add(new System.Data.SqlCompactDatabase.SqlCeColumn("LastModified", SqlDbType.DateTime));
            columns.Add(new System.Data.SqlCompactDatabase.SqlCeColumn("Size", SqlDbType.BigInt));
            this.CreateTable("Files", columns);

            this.EmptyTable("Files");

            columns.Clear();
            columns.Add(new SqlCeColumn("filePath", SqlDbType.NVarChar, 255));
            columns.Add(new SqlCeColumn("message", SqlDbType.NVarChar, 400));
            this.CreateTable("Errors", columns);

            this.EmptyTable("Errors");
        }
    }
}
